﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.IO;
using System.Xml;
using System.Text.RegularExpressions;
using Data.Abstract;
using Data.Configuration;
using System.Data.SQLite;
namespace Data
{
    public class DalPosicion
    {
        #region Propiedades
        Context _Context = new Context();
        SQLiteConnection _Conn;
        #endregion
        #region Procedimientos
        public DalPosicion() { }
        public DalPosicion(SQLiteConnection conn)
        {
            this._Conn = conn;
        }
        #endregion
        #region Metodos
       
        public void Insert(SimplePosicion simple)
        {
            using (SQLiteConnection conn = this._Conn)
            using (SQLiteCommand cmd = new SQLiteCommand(conn))
            {
                if (!CheckTableExist())
                {
                    CreateTable();
                }
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "INSERT INTO Posicion (Calle,Numero,Depto,Localidad,Provincia,TerrNum,Lat,Long,Nota,IdPersona)values(@Calle,@Numero,@Depto,@Localidad,@Provincia,@TerrNum,@Lat,@Long,@Nota,@IdPersona)";

                cmd.Parameters.Add("@Calle", DbType.String);
                cmd.Parameters.Add("@Numero", DbType.Int32);
                cmd.Parameters.Add("@Depto", DbType.String);
                cmd.Parameters.Add("@Localidad", DbType.String);
                cmd.Parameters.Add("@Provincia", DbType.String);
                cmd.Parameters.Add("@TerrNum", DbType.Int32);
                cmd.Parameters.Add("@Lat", DbType.Decimal);
                cmd.Parameters.Add("@Long", DbType.Decimal);
                cmd.Parameters.Add("@Nota", DbType.String);
                cmd.Parameters.Add("@IdPersona", DbType.Int32);

                cmd.Parameters[0].Value = simple.Calle;
                cmd.Parameters[1].Value = simple.Numero;
                cmd.Parameters[2].Value = simple.Depto;
                cmd.Parameters[3].Value = simple.Localidad;
                cmd.Parameters[4].Value = simple.Provincia;
                cmd.Parameters[5].Value = simple.TerrNum;
                cmd.Parameters[6].Value = simple.Lat;
                cmd.Parameters[7].Value = simple.Long;
                cmd.Parameters[8].Value = simple.Nota;
                cmd.Parameters[9].Value = simple.IdPersona;

                conn.Open();
                cmd.ExecuteNonQuery();

            }

        }
        public void Update(SimplePosicion simple)
        {
            using (SQLiteConnection conn = this._Conn)
            using (SQLiteCommand cmd = new SQLiteCommand(conn))
            {
                if (CheckTableExist())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "Update Posicion set Calle = @Calle,Numero = @Numero,Depto = @Depto,Localidad=@Localidad,Provincia=@Provincia,TerrNum = @TerrNum,Lat = @Lat,Long = @Long,Nota = @Nota,IdPersona = @IdPersona where Id = @Id";


                    cmd.Parameters.Add("@Id", DbType.Int32);
                    cmd.Parameters.Add("@Calle", DbType.String);
                    cmd.Parameters.Add("@Numero", DbType.Int32);
                    cmd.Parameters.Add("@Depto", DbType.String);
                    cmd.Parameters.Add("@Localidad", DbType.String);
                    cmd.Parameters.Add("@Provincia", DbType.String);
                    cmd.Parameters.Add("@TerrNum", DbType.Int32);
                    cmd.Parameters.Add("@Lat", DbType.Decimal);
                    cmd.Parameters.Add("@Long", DbType.Decimal);
                    cmd.Parameters.Add("@Nota", DbType.String);
                    cmd.Parameters.Add("@IdPersona", DbType.Int32);

                    cmd.Parameters[0].Value = simple.Id;
                    cmd.Parameters[1].Value = simple.Calle;
                    cmd.Parameters[2].Value = simple.Numero;
                    cmd.Parameters[3].Value = simple.Depto;
                    cmd.Parameters[4].Value = simple.Localidad;
                    cmd.Parameters[5].Value = simple.Provincia;
                    cmd.Parameters[6].Value = simple.TerrNum;
                    cmd.Parameters[7].Value = simple.Lat;
                    cmd.Parameters[8].Value = simple.Long;
                    cmd.Parameters[9].Value = simple.Nota;
                    cmd.Parameters[10].Value = simple.IdPersona;
                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
            }
        }
        public void CreateTable()
        {
            using (SQLiteConnection conn = this._Conn)
            using (SQLiteCommand cmd = new SQLiteCommand(conn))
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "CREATE TABLE Posicion (Id INTEGER PRIMARY KEY AUTOINCREMENT,  Calle varchar NOT NULL, Numero int NOT NULL, Depto varchar, Localidad varchar NOT NULL, Provincia varchar NOT NULL, TerrNum int NOT NULL, Lat numeric, Long numeric, Nota varchar, IdPersona int NOT NULL )";
                conn.Open();
                cmd.ExecuteNonQuery();
            }


        }
        public DataTable GetByPersonaId(int IdPersona)
        {
            DataTable Out = new DataTable();
            if (CheckTableExist())
            {

                using (SQLiteConnection conn = this._Conn)
                using (SQLiteCommand cmd = new SQLiteCommand(conn))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "Select * from Posicion where IdPersona = @IdPersona ";

                    cmd.Parameters.Add("@IdPersona", DbType.Int32);
                    cmd.Parameters[0].Value = IdPersona;

                    conn.Open();
                    SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd);
                    sda.Fill(Out);

                }
            }
            return Out;
        }
        public DataTable GetAll()
        {
            DataTable Out = new DataTable();

            if (CheckTableExist())
            {
                using (SQLiteConnection conn = this._Conn)
                using (SQLiteCommand cmd = new SQLiteCommand(conn))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "Select * from Posicion";
                    conn.Open();
                    SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd);
                    sda.Fill(Out);
                }
            }
            return Out;
        }
        public bool CheckTableExist()
        {

            DataTable Out = new DataTable();
            using (SQLiteConnection conn = this._Conn)
            using (SQLiteCommand cmd = new SQLiteCommand(conn))
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = " SELECT * FROM sqlite_master WHERE name = 'Posicion' AND type = 'table'";
                conn.Open();
                SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd);
                sda.Fill(Out);
                bool SchemaOk = (Out.Rows.Count == 0) ? false : true;
                return SchemaOk;
            }
        }

        #endregion
    }
}